#include "services.h"
#include "../sqlmodel/lqsqlrelationaltablemodel.h"
#include "../sqlmodel/sqlquerymodel.h"
#include "../model_transaction/model_transaction.h"
#include <QSqlQuery>
#include <QVariant>
#include <QDebug>
#include <QCryptographicHash>
#include <QDateTime>

extern QString _dbc_driver, _dbc_host, _dbc_db, _dbc_user, _dbc_passwd;

Services::Services(QQmlContext *ctxt, Session *sess)
{
    m_ctxt = ctxt;
    m_ses = sess;
    _auth_model = nullptr;
    _talk_model = nullptr;
    _warn_model = nullptr;
    _bc_model = nullptr;
    _user_model = nullptr;
    _query = nullptr;
    _isRunning = false;
}



int Services::isRunning(void) const {
    return _isRunning;
}
bool Services::start(void) {
    //if(m_con.doConnect("QMYSQL","localhost","qml_ui_test","dba01","1")) {
    if(m_con.doConnect(_dbc_driver, _dbc_host, _dbc_db, _dbc_user, _dbc_passwd)) {
        _auth_model = new LQSqlRelationalTableModel(nullptr, m_con.con());
        _talk_model = new LQSqlRelationalTableModel(nullptr, m_con.con());
        _warn_model = new LQSqlRelationalTableModel(nullptr, m_con.con());
        _bc_model = new LQSqlRelationalTableModel(nullptr, m_con.con());
        _user_model = new SqlQueryModel(nullptr);
        _query = new QSqlQuery(m_con.con());
        _isRunning = true;
        return true;
    }else {
        qDebug() << "Connect to database failed!!!" << endl;
        return false;
    }
}

void Services::stop(void) {
    delete _auth_model;
    delete _talk_model;
    delete _warn_model;
    delete _bc_model;
    delete _user_model;
    delete _query;
    m_con.close();
}

bool Services::doLogin(QString username, QString password)
{
    password = QString("").
            prepend(QCryptographicHash::hash(password.toLocal8Bit(),
                                             QCryptographicHash::Md5).toHex());
    _query->exec(QString("Select * from users where \"Username\" = '%1' and \"Password\" = '%2'").
                arg(username).
                arg(password));
    return _query->next();
}

SqlQueryModel *Services::getUserModel(void) {
    QString query("select  \"RoleName\", \"UID\", \"Name\", useradd_time  from"
                  "(select *, users.\"UserID\" as \"UID\" from users join userinrole on userinrole.\"UserID\" = users.\"UserID\")"
                  "as A join roles on roles.id = A.\"RoleID\"");
    _user_model->setQuery(query, m_con.con());
    return _user_model;

}


LQSqlRelationalTableModel *Services::getBroadcastlogModel(QString bc_type,  QString bc_time) {
    QString filter;
    QString endtime;
    _bc_model->setTable("broadcast_log");
    if(bc_type != "%") {
        filter = QString("bc_type='%1'").arg(bc_type);
    }

    if(bc_time != "%") {
        endtime = QDate::fromString(bc_time,"yyyy-MM-dd").
                    addDays(1).
                    toString("yyyy-MM-dd");
        filter += QString("%1 bc_time>='%2' and bc_time<'%3'").
                    arg(filter.isEmpty() ?"": "and").
                    arg(bc_time).
                    arg(endtime);
    }
    _bc_model->setFilter(filter);
    if(_bc_model->select()) {
        qDebug() << "init/update warnlog model succeed!";
    }
    return _bc_model;

}

LQSqlRelationalTableModel *Services::getWarnlogModel(QString libname,
                                                     QString warntype,
                                                     QString warntime) {

    QString filter;
    QString endtime;
    _warn_model->setTable("warn_log");
    if(libname != "%") {
        filter = QString("libname='%1'").arg(libname);
    }
    if(warntype != "%") {
        filter += QString("%1 warn_type='%2'").
                    arg(filter.isEmpty() ?"": "and").
                    arg(warntype);

    }
    if(warntime != "%") {
        endtime = QDate::fromString(warntime,"yyyy-MM-dd").
                    addDays(1).
                    toString("yyyy-MM-dd");
        filter += QString("%1 warn_time>='%2' and warn_time<'%3'").
                    arg(filter.isEmpty() ?"": "and").
                    arg(warntime).
                    arg(endtime);
    }
    _warn_model->setFilter(filter);
    if(_warn_model->select()) {
        qDebug() << "init/update warnlog model succeed!";
    }
    return _warn_model;
}

LQSqlRelationalTableModel *Services::getTalklogModel(QString libname,
                                                     QString talktime) {

    QString filter;
    QString endtime;
    _talk_model->setTable("talk_log");
    if(libname != "%") {
        filter = QString("libname='%1'").arg(libname);
    }
    if(talktime != "%") {
        endtime = QDate::fromString(talktime,"yyyy-MM-dd").
                  addDays(1).
                  toString("yyyy-MM-dd");
        filter += QString("%1 talk_time>='%2' and talk_time<'%3'").
                  arg(filter.isEmpty() ?"": "and").
                  arg(talktime).
                  arg(endtime);
    }
    _talk_model->setFilter(filter);
    if(_talk_model->select()) {
        qDebug() << "init/update talkmodel succeed!";
    }

    return _talk_model;
}

LQSqlRelationalTableModel *Services::getAuthlogModel(QString libname,
                                                     QString authtime) {

    QString filter;
    QString endtime;
    _auth_model->setTable("auth_log");
    if(libname != "%") {
        filter = QString("libname='%1'").arg(libname);
    }
    if(authtime != "%") {
        endtime = QDate::fromString(authtime,"yyyy-MM-dd").
                  addDays(1).
                  toString("yyyy-MM-dd");
        filter += QString("%1 auth_time>='%2' and auth_time<'%3'").
                  arg(filter.isEmpty() ?"": "and").
                  arg(authtime).
                  arg(endtime);
    }

    _auth_model->setFilter(filter);
    if(_auth_model->select()) {
        qDebug() << "init/update authlog model succeed!";
    }
    return _auth_model;
}


void Services::changeName(QString username)
{

    _query->prepare("Select Name from users where username = ?");
    _query->bindValue(0,username);
    _query->exec();
    if(_query->next())m_ses->setName(_query->value("Name").toString());
}



QStringList Services::getLibRoomNames()
{
    QStringList data;

    _query->exec("select name from libnames");
    while(_query->next())
    {
        data<<_query->value(0).toString();
    }
    return data;
}

QStringList Services::getBroadcastFileNames()
{
    QStringList data;

    _query->exec("select * from broadcast_file");
    while(_query->next())
    {
        data<< _query->value(0).toString()+"/"+_query->value(1).toString();
    }
    return data;
}
bool Services::delBroadCastFileName(QString fname) {
    return _query->exec(QString("delete from broadcast_file where filename='%1'").arg(fname));


}

bool Services::addBroadCastFileName(QString fname) {
    return _query->exec(QString("insert into broadcast_file (filename) values ('%1')").arg(fname));

}

QStringList Services::getTeams()
{
    QStringList data;
    //_query->exec("select name from teams");
    _query->exec("select name from libnames");
    while(_query->next())
    {
        data<<_query->value(0).toString();
    }
    return data;
}




QStringList Services::getBroadcastTypes()
{
    QStringList data;
    _query->exec("select distinct bc_type from broadcast_log");
    while(_query->next())
    {
        data<<_query->value(0).toString();
    }
    return data;
}

bool Services::update_warn_status(int id) {
    return _query->exec(
                QString("update warn_log set warn_status='已读' where id=%1").
                    arg(id));

}



